//
//  Database.m
//  test
//
//  Created by Ruj on 11/14/13.
//  Copyright (c) 2013 Ruj. All rights reserved.
//

#import "Database.h"


// For storing shared instance of the database
static Database *sharedInstance = nil;

@implementation Database

bool createDB(){
    Database *db = [Database new];
    
    bool isSuccess = YES;
    
    NSFileManager* fm = [NSFileManager defaultManager];
    NSError* err = nil;
    
    // Get url for directory to store our database
    NSURL* docsurl =
    [fm URLForDirectory:NSApplicationSupportDirectory
               inDomain:NSUserDomainMask appropriateForURL:nil
                 create:YES error:&err];
    
    // Get path string for our file
    db->databasePathString = [docsurl URLByAppendingPathComponent:@"record.db"].path;
    
    // Check if file exist
    if ([fm fileExistsAtPath: db->databasePathString ] == NO)
    {
        // File doesn't exist we have to create a new file
        
        const char *databasePath = [db->databasePathString UTF8String];
        if (sqlite3_open(databasePath, &(db->database)) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS 'DriveData' (\
            'Record' INTEGER NOT NULL , \
            'Time' DATETIME NOT NULL , \
            'Lat' DOUBLE NOT NULL, \
            'Long' DOUBLE NOT NULL, \
            'Force' DOUBLE NOT NULL , \
            PRIMARY KEY ('Record', 'Time'))";
            if (sqlite3_exec(db->database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create DriveData table");
            }
            
            sql_stmt = "CREATE TABLE IF NOT EXISTS 'RecordSet' (\
            'Record' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , \
            'Time' DATETIME NOT NULL )";
            
            if (sqlite3_exec(db->database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create RecordSet table");
            }
            
            sqlite3_close(db->database);
            
            // Store shared when successful
            sharedInstance = db;
            
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
    }else{
        sharedInstance = db;
    }
    
    return isSuccess;
}

+(Database*)getSharedInstance{
    if (sharedInstance == nil) {
        createDB();
    }
    return sharedInstance;
}

// Create record and return the record number
-(NSInteger)createRecord:(NSDate*)date{
    NSInteger recordNumber = -1;
    
    if (sqlite3_open([databasePathString UTF8String], &database) == SQLITE_OK)
    {
        char *errMsg;
        
        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO 'RecordSet'(Time) VALUES ('%f')",date.timeIntervalSince1970];
        const char *sql_stmt = [insertSQL UTF8String];
        
        if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
        {
            NSLog(@"Failed to create RecordSet table");
        }else{
            // Get the latest record number
            NSString *lookupSQL = [NSString stringWithFormat:@"SELECT MAX(Record) FROM RecordSet"];
            sql_stmt = [lookupSQL UTF8String];
            
            sqlite3_prepare_v2(database, sql_stmt,-1, &statement, NULL);
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                recordNumber = sqlite3_column_int(statement, 0);
            }
            
            sqlite3_finalize(statement);
            sqlite3_close(database);
        }
    }
    return recordNumber;
}

// Record driving force
-(BOOL)saveRecordData:(NSInteger) recordID
            timestamp:(NSDate*) timestamp
             location:(CLLocation*) location
                force:(double)force{
    
    if (sqlite3_open([databasePathString UTF8String], &database) == SQLITE_OK)
    {
        char *errMsg;
        
        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO DriveData(Record,Time,Lat,Long,Force) VALUES ('%d','%f','%f','%f','%f')",
                               recordID,
                               timestamp.timeIntervalSince1970,
                               location.coordinate.latitude,
                               location.coordinate.longitude,
                               force];
        
        const char *sql_stmt = [insertSQL UTF8String];
        
        if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
        {
            NSLog(@"Failed to create RecordSet table");
        }
        sqlite3_close(database);
    }
    
    return false;
}

// Find drive records to display in Table
-(NSDate*)getDriveRecord:(NSInteger) recordID{
    NSDate* result = nil;
    
    if (sqlite3_open([databasePathString UTF8String], &database) == SQLITE_OK)
    {
        // Get the latest record number
        NSString *lookupSQL = [NSString stringWithFormat:@"SELECT Time FROM RecordSet WHERE Record = %i", recordID];
        const char *sql_stmt = [lookupSQL UTF8String];
        
        sqlite3_prepare_v2(database, sql_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            double timeStamp = sqlite3_column_double(statement, 0);
            
            result = [NSDate dateWithTimeIntervalSince1970:timeStamp];
        }
        
        sqlite3_finalize(statement);
        sqlite3_close(database);
        
    }
    
    return result;
}

-(NSInteger)countRecords{
    NSInteger recordNumber = -1;
    
    if (sqlite3_open([databasePathString UTF8String], &database) == SQLITE_OK)
    {
        // Get the latest record number
        NSString *lookupSQL = @"SELECT count(Record) FROM RecordSet";
        const char *sql_stmt = [lookupSQL UTF8String];
        
        sqlite3_prepare_v2(database, sql_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            recordNumber = sqlite3_column_int(statement, 0);
        }
        
        sqlite3_finalize(statement);
        sqlite3_close(database);
        
    }
    return recordNumber;
}

-(NSMutableArray*)getDriveRecordDetail:(NSInteger) recordID{
    NSMutableArray* result = [NSMutableArray new];
    
    if (sqlite3_open([databasePathString UTF8String], &database) == SQLITE_OK)
    {
        // Get the latest record number
        NSString *lookupSQL = [NSString stringWithFormat:@"SELECT Avg(force), Max(time) AS mtime, lat, long FROM DriveData WHERE Record = %i GROUP BY lat,long ORDER BY mtime", recordID];
        const char *sql_stmt = [lookupSQL UTF8String];
        
        sqlite3_prepare_v2(database, sql_stmt,-1, &statement, NULL);
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            DriveData *data = [DriveData new];
            data->force = sqlite3_column_double(statement, 0);
            data->time  = sqlite3_column_double(statement, 1);
            data->latitude = sqlite3_column_double(statement, 2);
            data->longtitude = sqlite3_column_double(statement, 3);
            
            [result addObject:data];
        }
        
        sqlite3_finalize(statement);
        sqlite3_close(database);
        
    }
    return result;
}

@end
